Order - Aware Etl Workflows

Tziovara, Vasiliki, A. MSc, Computer Science Department, University of Ioannina, Greece. October, 2006. Order-Aware ETL Workflows. Thesis Supervisor: Panos Vassiliadis. Data Warehouses are collections of data coming from different sources, used mostly to support decision making and data analysis in an organization. To populate a data warehouse with up-to-date records that are extracted from the sources, special tools are employed, called Extraction – Transform – Load (ETL) tools, which organize the steps of the whole process as a workflow. An ETL workflow can be considered as a directed acyclic graph (DAG) used to capture the flow of data from the sources to the data warehouse. The nodes of the graph are activities that apply transformations or cleansing procedures on data or recordsets used for storage purposes. The edges of the graph are input/output relationships between the nodes. The workflow is an abstract design at the logical level, which has to be implemented physically, i.e., to be mapped to a combination of executable programs/scripts that perform the ETL workflow. Each activity of the workflow can be implemented physically using various algorithmic methods, each with different cost in terms of time requirements or system resources (e.g., memory, space on disk, etc.). The objective of this work is to identify the best possible implementation of a logical ETL workflow. For this reason, we employ (a) a library of templates for the activities and (b) a set of mappings between logical and physical templates. First, we use a simple cost model, that computes as optimal, the scenario with the best expected execution speed. In this work, we model the problem as a state-space search problem and propose an exhaustive algorithm for state generation to discover the optimal physical implementation of the scenario. To this end, we propose a cost model as a discrimination criterion between physical representations, which works also for black-box activities with unknown semantics. We also study the effects of possible system failures to the workflow operation. The difficulty in this case, lies at the